package com.apestech.framework.sql.modul;

import com.apestech.framework.json.JSON;
import com.apestech.framework.json.JSONArray;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.json.JSONOrder;
import com.apestech.framework.sql.SqlUtil;
import com.apestech.framework.util.StringUtil;
import com.apestech.framework.util.callback.SpiltStrCallback;

import java.util.ArrayList;

/**
 * @author hup
 * @create 2019-04-24-16:09
 */
public class SqlStru  extends SqlUtil {
    public class Sub {
        private String id;
        private Integer pos;

        public Sub(String id, Integer pos){
            this.id=id.toUpperCase();
            this.pos=pos;
        }

        public String getId() {
            return id;
        }

        public Integer getPos() {
            return pos;
        }
    }

    public class Keyword{
        private String id;
        private Integer pos;
        private ArrayList<Sub> idList=new ArrayList<Sub>();

        public Keyword(String id, Integer pos) {
            this.id = id.toUpperCase();
            this.pos=pos;
        }

        public String getId() {
            return id;
        }

        public void addSub(Sub sub){
            idList.add(sub);
        }

        public ArrayList<Sub> getIdList() {
            return idList;
        }

        public void setIdList(ArrayList<Sub> idList) {
            this.idList = idList;
        }

        public Integer getPos() {
            return pos;
        }
    }

    private class SubPos {
        private String key1;
        private Integer pos1;
        private String key2;
        private Integer pos2;
        private String key3;
        private Integer pos3;
        private String key4;
        private Integer pos4;
        private String key5;
        private Integer pos5;

        public String getKey1() {
            return key1;
        }

        public Integer getPos1() {
            return pos1;
        }

        public void setKey1(String key1, Integer pos1) {
            this.key1 = key1;
            this.pos1 = pos1;
        }

        public String getKey2() {
            return key2;
        }

        public Integer getPos2() {
            return pos2;
        }

        public void setKey2(String key2, Integer pos2) {
            this.key2 = key2;
            this.pos2 = pos2;
        }

        public String getKey3() {
            return key3;
        }

        public Integer getPos3() {
            return pos3;
        }

        public void setKey3(String key3, Integer pos3) {
            this.key3 = key3;
            this.pos3 = pos3;
        }

        public String getKey4() {
            return key4;
        }

        public Integer getPos4() {
            return pos4;
        }

        public void setKey4(String key4, Integer pos4) {
            this.key4 = key4;
            this.pos4 = pos4;
        }

        public String getKey5() {
            return key5;
        }

        public Integer getPos5() {
            return pos5;
        }

        public void setKey5(String key5, Integer pos5) {
            this.key5 = key5;
            this.pos5 = pos5;
        }

        public String getKey(){
            return key1+":"+pos1;
        }

        public Integer getLastPos(){
            if(pos5!=null) return pos5;
            else if(pos4!=null) return pos4;
            else if(pos3!=null) return pos3;
            else if(pos2!=null) return pos2;
            return pos1;
        }

        public boolean isHasKey(String key){
            if(key.equals(key5)) return true;
            if(key.equals(key4)) return true;
            if(key.equals(key3)) return true;
            if(key.equals(key2)) return true;
            if(key.equals(key1)) return true;
            return false;
        }

    }

    private class Pos {
        private ArrayList<SubPos> sublist=new ArrayList<SubPos>();
        private ArrayList<Sub> listS=new ArrayList<Sub>();  //sql
        private ArrayList<Sub> listR=new ArrayList<Sub>();  //(
        int posL=-1;
        int posR=-1;
        private JSONObject subjson=new JSONObject();
        private boolean union=false;
        int subsize=-1;

        public void setKeyPos(String key, Integer pos) throws Exception {
            switch (key){
                case "SELECT":{
                    addSublist(key, pos);
                    listS.add(new Sub(key, pos));
                    break;
                }
                case "(":{
                    listR.add(new Sub(key, pos));
                    break;
                }
                case "UNION":{  //UNION ALL
                    addSublist(key, pos);
                    listS.add(new Sub(key, pos));
                    union=true;
                    break;
                }
                default: {
                    if(key.equals(")")){
                        posL=pos;
                        posR=listR.get(listR.size()-1).getPos();
                        listR.remove(listR.size()-1);
                    } else {
                        int slen=listS.size();
                        SubPos sub=null;
                        if(slen>0){
                            Sub bsub=listS.get(slen-1);
                            Integer lpos=bsub.getPos();
                            sub=getSubPos(bsub.getId()+":"+bsub.getPos());
                            Integer bpos=null;
                            int blen=listR.size();
                            if(blen>0) bpos=listR.get(listR.size()-1).getPos();
                            SubPos sub2=getSub(key, pos, bpos, sub);
                            sub=sub2;
                        } else sub=getFirst();
                        setPos(sub, key, pos);
                    }
                }
            }
        }

        private SubPos getSub(String key, Integer pos, Integer bpos, SubPos sub){
            if(isSub( key, pos, bpos, sub)) return sub;
            int len=sublist.size();
            for(int i=len-1; i>=0; i--){
                if(isSub( key, pos, bpos, sublist.get(i))) return sublist.get(i);
            }
            return null;
        }

        private boolean isSub(String key, Integer pos, Integer bpos, SubPos sub){
            //pos当前变量   bpos(   posL)  lpos最后  fpos最前
            if(sub.getKey1().equals("UNION")) return false;
            if(sub.isHasKey(key)) return false;
            if(bpos==null && posR==-1) return true;
            Integer fpos = sub.getPos1();
            Integer lpos = sub.getLastPos();
            if(fpos==lpos) return true;
            if(bpos!=null){
                if(lpos>posL) return true;
                if(fpos>posR) return false;
                if(lpos<bpos) return true;
            } else {
                if(fpos>posR) return false;
                int index=sublist.indexOf(sub);
                if (listR.size()==0 && index!=0) return false;
                return true;
            }
            return true;
        }

        private void addSublist(String key, Integer pos){
            SubPos p=new SubPos();
            p.setKey1(key, pos);
            sublist.add(p);
            subjson.put(key+":"+pos, sublist.size()-1);
        }

        private SubPos getFirst(){
            if(sublist.size()==0) return null;
            return sublist.get(0);
        }

        private SubPos getSubPos(String key) throws Exception {
            Integer inx=subjson.getInteger(key);
            if(inx==null) return null;
            return sublist.get(inx);
        }

        private void setPos(SubPos sub, String key, Integer pos) throws Exception {
            Integer inx=subjson.getInteger(sub.getKey1()+":"+sub.getPos1());
            if(sub.getKey2()==null) sub.setKey2(key, pos);
            else if(sub.getKey3()==null) sub.setKey3(key, pos);
            else if(sub.getKey4()==null) sub.setKey4(key, pos);
            else if(sub.getKey5()==null) sub.setKey5(key, pos);
            sublist.set(inx, sub);
        }

        public int size(){
            if(subsize==-1){
                subsize=sublist.size();
            }
            return subsize;
        }
    }

    private ArrayList<Keyword> keyword=new ArrayList<Keyword>();
    private JSONObject keyjson=new JSONObject();
    private String osql;
    private String sql;
    private ArrayList<String> klist=new ArrayList<String>();
    private Pos sqlPos=new Pos();
    private String from;

    public SqlStru(String sql) throws Exception {
        osql=sql;
        this.sql=osql.toUpperCase();
        klist.add("SELECT");
        klist.add("FROM");
        klist.add("WHERE;ORDER;GROUP");
        init();
    }

    private void init() throws Exception {
        int len=sql.length();
        String text="";
        String skey=";SELECT;FROM;WHERE;ORDER;GROUP;UNION;UNION ALL;";
        for(int i=0; i<len; i++){
            String s=sql.substring(i,i+1);
            if(!" ".equals(s) && !"\n".equals(s) && !",".equals(s)){
                if("(".equals(s) || ")".equals(s)){
                    sqlPos.setKeyPos(s,i);
                }
                if(!s.equals("\t") && !s.equals("(") && !s.equals(")"))
                    text=text+s;
            } else {
                if(skey.indexOf(";"+text+";")>=0){
                    int inx=i-text.length();
                    sqlPos.setKeyPos(text, inx);
                }
                text="";
            }
        }
    }

    public ArrayList<From> getFrom() throws Exception {
        if(from==null){
            SubPos sub=sqlPos.getFirst();
            Integer i1=sub.getPos2();
            Integer i2=null;
            if(sub.getPos3()==null) {
                if(sqlPos.union){
                    SubPos sub1=sqlPos.sublist.get(1);
                    if(sub1.getKey().indexOf("UNION")>=0){
                        i1=0;
                        i2=sub1.getPos1()-1;
                    } else {
                        i2=sql.length();
                    }
                    String s=osql.substring(i1,i2);
                } else i2=sql.length();
            }
            else i2=sub.getPos3();
            if(i1==null || i1==-1 || i2==null || i2==-1 || i2<i1)
                throw new Exception("SQL格式不正确，请检查！");
            from=osql.substring(i1,i2);
            return getFromlist(from);
        }
        return null;
    }

    public String getFromStr(){
        return from;
    }

    private ArrayList<From> getFromlist(String text){
        text=text.trim();
        text=text.replace("\n"," ");
        ArrayList<From> froms=new ArrayList<From>();
        if(!"FROM ".equals(leftStr(text.toUpperCase(),5))){
            froms.add(new From(text,""));
            return froms;
        } else text=rightStr(text, text.length()-5).trim();
        int len=text.length();
        int l=0;
        int r=0;
        String txt="";
        String tab="";
        String alais="";
        for(int i=len; i>0; i--){
            String s=text.substring(i-1,i);
            switch (s){
                case "(":{
                    r++;
                    txt=s+txt;
                    if(r==l){
                        if(isCase(txt)) {
                            tab=txt;
                            txt="";
                            r=0;
                            l=0;
                            froms.add(0, new From(tab, alais));
                            tab="";
                            alais="";
                        }
                    }
                    break;
                }
                case ")": {
                    l++;
                    txt=s+txt;
                    break;
                }
                case " ": {
                    if(l>0){
                        txt=s+txt;
                    } else {
                        if(!" ".equals(leftStr(txt,1)) && isWord(txt.trim())){
                            if(!"".equals(alais)){
                                tab=txt.trim();
                                froms.add(0, new From(tab, alais));
                                txt="";
                                tab="";
                                alais="";
                            } else {
                                alais=txt.trim();
                            }
                            txt="";
                        } else txt=s+txt;
                    }
                    break;
                }
                case ",": {
                    if(l>0){
                        txt=s+txt;
                    } else {
                        if(isWord(txt.trim())){
                            tab=txt.trim();
                            froms.add(0, new From(tab, alais));
                            txt="";
                            tab="";
                            alais="";
                        } else if(!"".equals(alais)){
                            froms.add(0, new From(alais,""));
                            txt="";
                            tab="";
                            alais="";
                        }
                    }
                    break;
                }
                default: txt=s+txt;
            }
        }
        if(!"".equals(txt)){
            froms.add(0,new From(txt, alais));
        }
        return froms;
    }

    private boolean isCase(String text){  //判断（）部分的语句是否为标准的查询元素之一
        text=text.trim();
        text=text.toUpperCase();
        if("(".equals(leftStr(text, 1)) && ")".equals(rightStr(text, 1))){
            text=text.substring(1,text.length()-1).trim();
            if("SELECT ".equals(leftStr(text,7)) && text.indexOf("FROM")>0){
                return true;
            }
        }
        return false;
    }

    private boolean isWord(String text){   //判断单个词是否为TAB或者别名
        if("".equals(text.trim())) return false;
        if(text.indexOf(" ")>=0) return false;
        if(text.indexOf(",")>=0) return false;
        if(text.indexOf("'")>=0) return false;
        return notKeyword(text);
    }

    private boolean notKeyword(String text){
        text=text.toLowerCase();
        if(text.indexOf("select")>=0) return false;
        if(text.indexOf("from")>=0) return false;
        if(text.indexOf("where")>=0) return false;
        if(text.indexOf("order")>=0) return false;
        if(text.indexOf("group")>=0) return false;
        if(text.indexOf("when")>=0) return false;
        if(text.indexOf("case")>=0) return false;
        return true;
    }


















}
